--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

/*DROP TABLE se_pcp_repent;
CREATE TABLE se_pcp_repent AS (SELECT e.* FROM &erp.pcp_repent e WHERE 1=2);
DROP TABLE se_pcp_repdet;
CREATE TABLE se_pcp_repdet AS (SELECT d.* FROM &erp.pcp_repdet d WHERE 1=2);  */

DELETE FROM se_pcp_repent ;
DELETE FROM se_pcp_repdet ;




/*SELECT e.*,d.*
FROM   &erp.pcp_repent e, &erp.pcp_repdet d
WHERE e.rep_cod='ARG_UNF'
  AND d.parentenreg_id(+)=e.enreg_id */


 INSERT INTO se_pcp_repent
 (SELECT distinct
      'ARG_UNF' rep_cod,
      Trunc(SYSDATE,'YEAR') deb_dat,
      NULL fin_dat,
      null periode_val,
      null jour_dat,
      null combsto_id,
      NULL unfab_cod,
      NULL plan_cod,
      NULL cpte_cod,
      0 enreg_id,
      p.prodbase libre_val
  FROM uv_pcatalogues c, uv_pproduits p
  WHERE c.REPARTPDP IS NOT null AND c.REPARTPDP !=0
    AND c.prcomplet=p.id(+)
    AND p.codgestion IN (1,2)) ;

 DELETE FROM se_pcp_repent WHERE rep_cod='ARG_UNF' AND libre_val IS NULL ;
 DELETE FROM se_pcp_repent r WHERE NOT EXISTS (SELECT 1 FROM &erp.art_comp c WHERE c.gene_cod || c.gene_ind= r.libre_val) ;

 UPDATE se_pcp_repent tf SET enreg_id=Nvl((select Max(enreg_id) FROM &erp.pcp_repent f WHERE f.rep_cod=tf.rep_cod AND f.libre_val =tf.libre_val AND f.DEB_DAT=tf.DEB_DAT),0) ;
 UPDATE se_pcp_repent tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id=0 ;




 INSERT INTO se_pcp_repdet
 (SELECT distinct
      Nvl((SELECT enreg_id FROM &erp.pcp_repent e
       WHERE e.rep_cod='ARG_UNF'
         AND e.libre_val=p.prodbase
         AND e.deb_dat=trunc(SYSDATE,'YEAR')
         ),0)   parentenreg_id,
      NULL periode_val,
      NULL jour_dat,
      NULL combsto_id,
      c.four unfab_cod,
      NULL cpte_cod,
      NULL plan_cod,
      0 enreg_id,
      NULL repp_val,
      c.REPARTPDP repc_val,
      NULL libre_val
  FROM uv_pcatalogues c, uv_pproduits p
  WHERE c.REPARTPDP IS NOT null
    AND c.prcomplet=p.id(+)
    AND p.codgestion IN (1,2) ) ;

 DELETE FROM se_pcp_repdet WHERE parentenreg_id=0 ;

 UPDATE se_pcp_repdet tf SET enreg_id=Nvl((select enreg_id FROM &erp.pcp_repdet f WHERE f.parentenreg_id=tf.parentenreg_id AND f.unfab_cod=tf.unfab_cod),0) ;
 UPDATE se_pcp_repdet tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id=0 ;

DELETE FROM &erp.pcp_repdet gg WHERE EXISTS (SELECT 1 FROM &erp.pcp_repent g WHERE rep_cod='ARG_UNF' AND gg.parentenreg_id=g.enreg_id);
DELETE FROM &erp.pcp_repent gg WHERE rep_cod='ARG_UNF' ;

 CALL uvse_synchroverserp('parentenreg_id@unfab_cod','PCP_REPDET','O') ;
 CALL uvse_synchroverserp('rep_cod@deb_dat@libre_val','PCP_REPENT','O') ;


 commit;



select * from (
SELECT &outils.stragg(source) source,rep_cod, deb_dat, fin_dat, periode_val, jour_dat, combsto_id, unfab_cod, plan_cod, cpte_cod, enreg_id, libre_val
FROM (
  SELECT 'uv' source,e.* FROM   se_pcp_repent e
  WHERE e.rep_cod='AN_ARG_UNF'

  UNION ALL
  SELECT 'se' source,e.* FROM   &erp.pcp_repent e
  WHERE e.rep_cod='AN_ARG_UNF'
  )
GROUP BY rep_cod, deb_dat, fin_dat, periode_val, jour_dat, combsto_id, unfab_cod, plan_cod, cpte_cod, enreg_id, libre_val
HAVING Count(1)!=2) r
ORDER BY combsto_id ;